#!/usr/bin/env python
# encoding: utf-8
#################
# Name:        更新数据总览表， 每个更新ID1 ,周一更新ID0 
# Purpose:
#
# Author:      qfh 
#
# Created:     02/26/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
#import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone import Phone
from birth import Birth

reload(sys)
sys.setdefaultencoding('utf-8')
sys.path.append(os.environ['HOME']+"/lib")
from pyctlib import *
from func    import *


VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-26'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg

    self.table          = 'dat_summary'
    self.add_user_id    = 1
    self.update_user_id = 1
    self.__ph = Phone()
  
  def handle_data(self):
   
    #self.percent() 
    now  = datetime.datetime.now()
    week = datetime.datetime.isoweekday(now)
    logging.info('weekday is :%d'%(week) )
    if week == 1:
      db_dict = self.__db_dict_value() 
      cnt_week = self.__table_cnt(self.table,  'log_id=0', 1)
      
      if type(db_dict) == dict and len(db_dict)>1:
        db_dict['log_id'] = 0
        if cnt_week == 1:
          sql = generate_update_sql(db_dict, self.table) 
        else:
          sql = generate_insert_sql(db_dict, self.table)
        self.__update_db(sql)
  
    logging.info("update every daty task!") 
    ge_dict = self.__generate_dict_value()
    cnt_day = self.__table_cnt(self.table,  'log_id=1', 1)
    ge_dict['log_id'] = 1 
    if len(ge_dict)>1:
      if cnt_day == 1:
        sql = generate_update_sql(ge_dict, self.table)
      else:
        sql = generate_insert_sql(ge_dict, self.table)
      self.__update_db(sql)  
    
  def __db_dict_value(self):
    cnt = self.__table_cnt(self.table, "log_id=1",  1) 
    sql = "select * from %s where log_id=1"%(self.table)
    res = self.__select_db(sql, True)
    return convert_valid_dict (res)

  def __generate_dict_value(self):
    add_dict = {
            'total':0,'total_inner_api':0,'total_third_api':0,'total_import':0,\
            'num_api':0,'num_template':0,'total_in_dw':0,'total_user':0,'total_action':0,\
            'persona_complete_percent':0,'touchable_user_percent':0,'num_tag':0,\
             'num_tag_dict':0,'num_tag_custom':0,'num_tag_raw':0,'num_tag_stat':0,\
             'num_tag_model':0
             }


    #数据总量
    ret = self.total_cnt() 
    if ret.get('total_cnt'):
      add_dict['total'] = ret.get('total_cnt')
    else:
      add_dict['total'] = 0
    
    #内部接口数据总量 
    total_inner_api = ['yd_channel_base','yd_channel_type','yd_client_device',\
      'yd_client_device_login','yd_client_user','yd_dictionary_info',\
      'yd_intelligence_info','yd_intelligence_like','yd_intelligence_reply_list',\
      'yd_intelligence_user','yd_label_info','yd_news_comment','yd_news_detail',\
      'yd_news_info','yd_news_praise','yd_source_info','yd_user_sub_channel'] 
    ret = self.get_cnt(2, total_inner_api, 'total_inner_api') 
    add_dict['total_inner_api'] = ret
    del ret
 
    #导入数据总量',
    total_import = ['db_order_newspaper_info']                               
    ret = self.get_cnt(2,  total_import,'total_import')   
    add_dict['total_import'] = ret
    del ret
    
    #'接口数量',
    num_api = ['dat_api_sdk_info']
    ret = self.get_cnt(1, num_api, 'num_api') 
    add_dict['num_api'] = ret
    del ret
    
    # 模板数量                                                                
    num_template = ['dat_templet_info' ]
    ret = self.get_cnt(1, num_template, 'num_template')
    add_dict['num_template'] = ret
    del ret
                                                     
    
    #'入仓数据总量', 
    ret = self.total_in_dw()
    add_dict['total_in_dw'] = ret
    del ret
    
    #'用户规模'                                                               
    total_user = ['usr_user_basic_info']                                     
    ret = self.get_cnt(1, total_user, 'total_user') 
    add_dict['total_user'] = ret
    del ret
    
    #'行为数量规模',
    total_action = [ 'yd_news_comment', 'yd_intelligence_info', \
                     'yd_intelligence_reply_list', 'yd_client_device_login' ]
    ret = self.get_cnt(2,total_action, 'total_action')
    add_dict['total_action'] = ret
    del ret
    
     
    #'画像完整度百分比', '可触达用户百分比', 
    ret = self.percent()
    if ret.get('persona_complete_percent'): 
      add_dict['persona_complete_percent'] = ret.get('persona_complete_percent')
    else:
      add_dict['persona_complete_percent'] = 0 
    if ret.get('touchable_user_percent'):
      add_dict['touchable_user_percent']   = ret.get('touchable_user_percent') 
    else:
      add_dict['touchable_user_percent']   = 0
    del ret
    
       
    #'标签总量',
    num_tag = ['tg_tags_info']
    ret = self.get_cnt(1,num_tag, 'num_tag' ) 
    add_dict['num_tag'] = ret
    del ret
    
    #标签字典总量
    num_tag_dict = ['tg_tags_info'] 
    ret = self.get_cnt(1, num_tag_dict, 'num_tag_dict', 'class_id !=92')
    add_dict['num_tag_dict'] = ret
    del ret
    
    #自定义标签总量',
    num_tag_custom = ['tg_tags_info']
    ret = self.get_cnt(1,num_tag_custom, 'num_tag_custom', 'class_id=92' )   
    add_dict['num_tag_custom'] = ret
    del ret
    
    #'原始数据标签总量
    num_tag_raw = ['tg_tags_info']
    ret= self.get_cnt(1,num_tag_raw, 'num_tag_raw', 'tag_type=1' ) 
    add_dict['num_tag_raw'] = ret
    del ret
    
    #'统计标签总量', 
    num_tag_stat = ['tg_tags_info']
    ret = self.get_cnt(1,num_tag_stat, 'num_tag_stat', 'tag_type=2' )
    add_dict['num_tag_stat'] = ret
    del ret
    
    #'模型算法标签总量',
    num_tag_model = ['tg_tags_info']
    ret = self.get_cnt(1,num_tag_model, 'num_tag_model', 'tag_type=3' ) 
    add_dict['num_tag_model'] = ret
    del ret
   
    return add_dict




  def get_cnt(self, cnt_type, tabs, col, wh=None ):
    if type(tabs) != list or type(col)!=str:
      return 0
    if len(tabs) <1 or len(col)<1:
      return 0 
    cnt = 0
    for tab in tabs:
      cnt += self.__table_cnt(tab, wh, cnt_type)

    return cnt 

  #cnt_type  1, select count  2. select table_schema 
  def __table_cnt(self, table=None, wh=None, cnt_type = 1):
    if cnt_type   == 1:
      ret = self.__table_cnt_count(table, wh)
    elif cnt_type == 2:
      ret = self.__table_cnt_schem(table)
    else:
      ret = 0
    return ret
 
 

  #通过select count 查询数据
  def __table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro') 
    sql = "select count(1) from %s "%(table)  
    if wh:
      sql +=  'where %s'%( wh)

    res = self.__select_db(sql) 
    #print( res )
    return res[0][0]  

  #通过数据字典查询数据cnt
  def __table_cnt_schem(self, tabname=None ):
    sql = "select table_name, table_rows  from information_schema.tables where\
           table_schema='qilu_data_new' and table_type='base table'";
   
    try:                                                                     
      cursor = self.dbconn.cursor( pymysql.cursors.DictCursor ) 

      cursor.execute(sql)                                                    
      res = cursor.fetchall()                                                
      cursor.close()   
      cnt = 0
      for dict_v  in res:                                                    
        if tabname:                                                          
          if dict_v.get('table_name') and dict_v.get('table_name') == tabname:
            if dict_v.get('table_rows') and int(dict_v.get('table_rows'))>0 :
              cnt += int(dict_v.get('table_rows'))                           
        else:                                                                
          if dict_v.get('table_rows') and int(dict_v.get('table_rows'))>0:   
            cnt += int(dict_v.get('table_rows'))   
      return cnt

    except Exception,err:
      logging.warning('err:%s' %(err) )     


  def total_cnt(self, tabname=None):
    add_dict= {} 
    try:                                                                     
      cnt =  self.__table_cnt_schem()
      if not cnt: 
        logging.error('select total_cnt errro')
        return
      if cnt>0:
        add_dict['total_cnt']  = cnt
           
    except Exception,err:
      logging.warning('err:%s' %(err) )       
    return add_dict 
   
 

  def percent(self):
    add_dict={}

    #get mobile, user_sex... 等需要入usr_user_basic_info的字段
    sql = 'select rel_column from tg_tags_class_info where rel_table=1'
    res = self.__select_db(sql, True)

    sql = 'select  ' 
    for re in res:
      if  re.get('rel_column'):
        sql += re.get('rel_column') + ','
    sql += 'user_id '
    sql += ' from usr_user_basic_info '
   
    valid_persona   = 0  #画像完整度用户，有效字段大于8
    valid_touchable = 0  #可触达用户  手机号和iMEI有效 

    res = self.__select_db(sql, dictionary=True)
    for re in res: 
      valid_1 = 0
      valid_2 = 0
     
      for d in re.values():
        if d is not None:
          valid_1 += 1
       
      if len(str(re.get('mobile')))==11  and self.__ph.find( str(re.get('mobile')).strip()):
        valid_2 += 1
      if re.get('email') and str(re.get('email')).find('@') != -1:
        valid_2 += 1

      if valid_1 <8 and re.get('user_id'):
        ct = self.__table_cnt( 'usr_user_tag_relation', 'user_id = %d'%( re.get('user_id')))
        if ct >0:
          valid_1 += ct 
        
      logging.info("update user:%d tag_count:%d"%(re.get('user_id'), valid_1) )
      sql = "update usr_user_basic_info  set tag_count=%s ,\
             update_user_id = %d, update_time='%s' where user_id=%s"%( valid_1,\
              1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 
             re.get('user_id') )
      logging.info(sql)
      self.__update_db(sql)
         
      if valid_1 >=8:
        valid_persona   += 1
      if valid_2 >0:
        valid_touchable += 1
      
    u_cnt = self.__table_cnt( 'usr_user_basic_info' )  
    persona_per = float(valid_persona)/float(u_cnt)*100 
    touch_per   = float(valid_touchable)/float(u_cnt)*100 
    #print('persona_per:%f touch_per:%f ' %(persona_per, touch_per ) )   
    add_dict['persona_complete_percent']  = persona_per
    add_dict['touchable_user_percent']    = touch_per
  
    return add_dict
    

  def total_in_dw(self):
    sql = 'select total_inner_api,total_third_api,total_import  from  \
           dat_summary where log_id=1'
    res = self.__select_db(sql, True)

    cnt = 0
    for re in res:
      if re.get('total_inner_api'):
        cnt += int(re.get('total_inner_api') )
      if re.get('total_third_api'): 
        cnt += int(re.get('total_third_api'))
      if re.get('total_import'):
        cnt += int( re.get('total_import') )
    
    return cnt 

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      #logging.info(sql)
      self.dbconn.commit()
      cursor.close()

    except Exception,err:
      logging.warning('err:%s' %(err) ) 

  def __select_db(self, sql, dictionary=None):                                                
    try:
      if dictionary:                                                                     
        cursor = self.dbconn.cursor(  pymysql.cursors.DictCursor ) 
      else:
        cursor = self.dbconn.cursor()                                       

      self.dbconn.commit()
      cursor.execute(sql)     
      res = cursor.fetchall()                                               
      cursor.close()
      return res

    except Exception,err:
      logging.warning('err:%s' %(err) )                                      




   

def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   
   obj = Handle(UDB,cfg)
   obj.handle_data()
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
